package com.OnlineHouseLeaseSystem.common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;

import com.OnlineHouseLeaseSystem.database.DBManager;

public class RentMessageManager {
	private static final String USER_ID = "user_id";
	private static final String MESSAGE_ID = "message_id";
	private static final String MESSAGE_TITLE = "message_title";
	private static final String REGION = "region";
	private static final String RENT = "rent";
	private static final String LOOK_TIME = "look_time";
	private static final String DESCRIPTION = "description";
	private static final String PHONE_NUMBER = "phone_number";
	
	private static final String USER_NAME = "user_name";
	
	private final static String LOCK_TABLE = "LOCK TABLE rent_message IN EXCLUSIVE MODE; ";
	
	private final static String GET_RENT_MESSAGE_AND_USER_INFORMATION 
									= "SELECT * FROM rent_message,user_information " +
									  "WHERE rent_message.user_id=user_information.user_id ORDER BY message_id DESC";
	private final static String GET_MAX_MESSAGE_ID = "SELECT MAX(message_id) FROM rent_message ";
	private final static String INSERT_RENT_MESSAGE_HEAD = "INSERT INTO rent_message VALUES ( '";
    private final static String INSERT_RENT_MESSAGE_FOOT = "' ) ";
    private final static String DELETE_RENT_MESSAGE = "DELETE FROM rent_message WHERE message_id='";
    private final static String UPDATE_RENT_MESSAGE_INFORMATION = "UPDATE rent_message SET ";
    private final static String SEARCH_RENT_MESSAGE_HEAD = "SELECT * FROM rent_message,user_information" +
    													   " WHERE rent_message.user_id=user_information.user_id "; 
    private final static String SEARCH_RENT_MESSAGE_FOOT = " ORDER BY message_id DESC ";
    
    public static ArrayList searchRentMessage(String message_title, String region, int rent_begin, int rent_end)
    {
    	ArrayList list = new ArrayList();
    	String searchword="";
    	if(!message_title.equals(""))
    	{
    		searchword+=" AND message_title LIKE '%" + message_title + "%'";
    	}
    	if(!region.equals("请选择"))
    	{
    		searchword+=" AND region='" + region + "'";
    	}
    	if(rent_begin != 0 && rent_end != 0)
    	{
    		searchword +=" AND rent>=" +rent_begin + " AND rent<=" + rent_end;
    	}
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(SEARCH_RENT_MESSAGE_HEAD + searchword + SEARCH_RENT_MESSAGE_FOOT);
            while (result.next()) 
            {
            	RentMessage rentMessage = new RentMessage();
    			rentMessage.setUser_id(result.getInt(USER_ID));
    			rentMessage.setMessage_id(result.getInt(MESSAGE_ID));
    			rentMessage.setMessage_title(result.getString(MESSAGE_TITLE));
    			rentMessage.setRegion(result.getString(REGION));
    			rentMessage.setRent(result.getInt(RENT));
    			rentMessage.setLook_time(result.getString(LOOK_TIME));
    			rentMessage.setDescription(result.getString(DESCRIPTION));
    			rentMessage.setUser_name(result.getString(USER_NAME));    			
                list.add(rentMessage);
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
	
	public static int getMessage_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE + GET_MAX_MESSAGE_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }

	public static void insertNewRentMessage(int user_id, String message_title,
											String region, int rent, String look_time, String description)
	{
		int message_id = getMessage_idForNew();
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(INSERT_RENT_MESSAGE_HEAD +
    				user_id + "','" + message_id + "','" + message_title + "','" + region + "','" +
    				 + rent + "','" + look_time + "','" + description  + INSERT_RENT_MESSAGE_FOOT);
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
	
	public static ArrayList getAllRentMessage()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_RENT_MESSAGE_AND_USER_INFORMATION);
    		while(result.next())
    		{
    			RentMessage rentMessage = new RentMessage();
    			rentMessage.setUser_id(result.getInt(USER_ID));
    			rentMessage.setMessage_id(result.getInt(MESSAGE_ID));
    			rentMessage.setMessage_title(result.getString(MESSAGE_TITLE));
    			rentMessage.setRegion(result.getString(REGION));
    			rentMessage.setRent(result.getInt(RENT));
    			rentMessage.setLook_time(result.getString(LOOK_TIME));
    			rentMessage.setDescription(result.getString(DESCRIPTION));
    			rentMessage.setUser_name(result.getString(USER_NAME));    			
                list.add(rentMessage);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
	
	public static ArrayList getUserRentMessage(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_RENT_MESSAGE_AND_USER_INFORMATION);
    		while(result.next())
    		{
    			if(result.getInt("user_id") == user_id)
    			{
					RentMessage rentMessage = new RentMessage();
					rentMessage.setUser_id(result.getInt(USER_ID));
					rentMessage.setMessage_id(result.getInt(MESSAGE_ID));
					rentMessage.setMessage_title(result.getString(MESSAGE_TITLE));
					rentMessage.setRegion(result.getString(REGION));
					rentMessage.setRent(result.getInt(RENT));
					rentMessage.setLook_time(result.getString(LOOK_TIME));
					rentMessage.setDescription(result.getString(DESCRIPTION));
					rentMessage.setUser_name(result.getString(USER_NAME));    			
		            list.add(rentMessage);
    			}
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
	
	public static RentMessage getRentMessage(int message_id)
	{
		RentMessage rentMessage = new RentMessage();
		try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_RENT_MESSAGE_AND_USER_INFORMATION);
    		while(result.next())
    		{
    			if(result.getInt(MESSAGE_ID) == message_id)
    			{
					rentMessage.setUser_id(result.getInt(USER_ID));
					rentMessage.setMessage_id(result.getInt(MESSAGE_ID));
					rentMessage.setMessage_title(result.getString(MESSAGE_TITLE));
					rentMessage.setRegion(result.getString(REGION));
					rentMessage.setRent(result.getInt(RENT));
					rentMessage.setLook_time(result.getString(LOOK_TIME));
					rentMessage.setDescription(result.getString(DESCRIPTION));
					rentMessage.setUser_name(result.getString(USER_NAME));
					rentMessage.setPhone_number(result.getString(PHONE_NUMBER));
					break;
    			}
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return rentMessage;
	}
	
	public static void rentMessageUpdate(int message_id, String message_title, String region, int rent,
										 String look_time, String description)
	{
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_RENT_MESSAGE_INFORMATION + "message_title='" + message_title 
    				+ "', region='" + region + "', rent='" + rent + "' WHERE message_id='" + message_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
	
	public static void deleteRentMessage(int message_id)
	{
		try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_RENT_MESSAGE + message_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
	}
}
